﻿using System.Data;
using System.IO;
using System.Web;
using System.Collections.Generic;
using System;
using System.Linq;
using System.Text;
using NPOI.SS.Util;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;

namespace Common
{
    /// <summary>
    /// Excel97操作相关辅助类
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="stream"></param>
        /// <param name="fileName">excel文件名</param>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable ReadExcelToDataTable(Stream stream, string fileName, string sheetName, bool isFirstRowColumn)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            try
            {
                IWorkbook workbook = null;
                //var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(stream);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(stream);
                else
                {
                    throw new Exception("请上传Excel文件！");
                }

                if (sheetName != null)
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }

                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                dataRow[j] = row.GetCell(j).ToString();
                        }
                        data.Rows.Add(dataRow);
                    }
                }

                return data;
            }
            catch (Exception ex)
            {
                throw ex;
                return null;
            }
        }

        public static void ExportToExcel(DataTable dt, string fileName)
        {
            try
            {
                var ds = new DataSet();
                ds.Tables.Add(dt);
                ExportToExcel(ds, fileName);
            }
            catch (Exception ex)
            {
            }
        }

        /// <summary>
        /// 将datable字段名转换为指定中文并导出Excel
        /// </summary>
        /// <param name="dataTable">数据集</param>
        /// <param name="dic">字段名</param>
        /// <returns></returns>
        public static void DataTableToExcel(DataTable dataTable, Dictionary<string, string> dic, string filename)
        {
            var edataTable = ConvertDatatable(dataTable, dic);
            ExportToExcel(edataTable, filename);
        }

        public static DataTable ConvertDatatable(DataTable dataTable, Dictionary<string, string> dic)
        {
            var edataTable = new DataTable();
            foreach (var item in dic.Keys)
            {
                var v = dic[item];
                edataTable.Columns.Add(v);
            }
            foreach (DataRow row in dataTable.Rows)
            {
                var newrow = edataTable.NewRow();
                var i = 0;
                foreach (var item in dic.Keys)
                {
                    var v = dic[item];
                    newrow[i++] = row[item];
                }
                edataTable.Rows.Add(newrow);
            }
            return edataTable;
        }

        public static void ExportToExcel(DataSet ds, string fileName)
        {
            IWorkbook wookBook = new XSSFWorkbook();

            ICellStyle headerStyle = wookBook.CreateCellStyle();
            var headerFont = wookBook.CreateFont();
            headerFont.FontHeightInPoints = 12;
            headerFont.FontName = "黑体";
            headerFont.Color = HSSFColor.BlueGrey.Index;
            headerStyle.SetFont(headerFont);

            foreach (DataTable dt in ds.Tables)
            {
                ISheet sheet = wookBook.CreateSheet(dt.TableName);
                IRow sheetColumn = sheet.CreateRow(0);

                //写入列头
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    var cell = sheetColumn.CreateCell(j);
                    cell.SetCellValue(dt.Columns[j].ColumnName);
                    cell.CellStyle = headerStyle;
                    sheet.AutoSizeColumn(j);//表格列宽自动调整,只能支持英文和数字
                }
                //写入行
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        string gbStr = Encoding.GetEncoding("gb2312").GetString(Encoding.Default.GetBytes(dt.Rows[i][j].ToString()));
                        row.CreateCell(j).SetCellValue(gbStr);
                    }
                }
            }
            var ms = new MemoryStream();
            wookBook.Write(ms);

            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xlsx");
            HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            HttpContext.Current.Response.End();
            ms.Flush();
            ms.Position = 0;
            ms.Close();
            ms = null;
        }

        public static void ExportToCsv(DataTable tblSchema, string fileName)
        {
            StringBuilder str = new StringBuilder();
            char c = ',';

            StringWriter sw = new StringWriter();
            for (int i = 0; i < tblSchema.Columns.Count; i++)
            {
                str.Append(tblSchema.Columns[i].Caption + c);
            }
            str.Append("\r\n");
            for (int i = 0; i < tblSchema.Rows.Count; i++)
            {
                for (int j = 0; j < tblSchema.Columns.Count; j++)
                {
                    if (tblSchema.Rows[i][j].ToString().ToUpper().Equals("TRUE"))
                        str.Append("是" + c);
                    else if (tblSchema.Rows[i][j].ToString().ToUpper().Equals("FALSE"))
                        str.Append("否" + c);
                    else
                        str.Append(tblSchema.Rows[i][j].ToString().Replace(",", "，").Replace("\r\n", "") + c);
                }
                str.Append("\r\n");
            }
            sw.Write(str);
            sw.Close();
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;   filename=" + fileName + ".csv");
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            HttpContext.Current.Response.Write(sw);
            sw.Dispose();
            HttpContext.Current.Response.End();
        }

        public static void ExportToExcel(IList<ExcelTable> ds, string fileName)
        {
            IWorkbook wookBook = new XSSFWorkbook();

            ICellStyle headerStyle = wookBook.CreateCellStyle();
            headerStyle.Alignment = HorizontalAlignment.Center;
            headerStyle.VerticalAlignment = VerticalAlignment.Center;
            var headerFont = wookBook.CreateFont();
            headerFont.FontHeightInPoints = 12;
            headerFont.FontName = "黑体";
            headerFont.Color = HSSFColor.BlueGrey.Index;
            headerStyle.SetFont(headerFont);

            foreach (ExcelTable dt in ds)
            {
                ISheet sheet = wookBook.CreateSheet(dt.TableName);
                sheet.CreateFreezePane(dt.FreezeCols, dt.FreezeRows);
                var headers = dt.Rows.Where(c => c.IsHeader).ToList();
                var headerCount = headers.Count();
                //写入列头
                for (int j = 0; j < headers.Count(); j++)
                {
                    IRow headerRow = sheet.CreateRow(j);
                    var nc = 0;
                    foreach (var tc in headers[j].Cells)
                    {
                        var hc = tc ?? new ExcelTableCell();

                        var cell = headerRow.CreateCell(nc);
                        cell.SetCellValue(hc.Value);
                        cell.CellStyle = headerStyle;
                        cell.SetCellType(hc.CellType);

                        if (hc.HasMerged())
                        {
                            var ec = nc + hc.Colspan;
                            sheet.AddMergedRegion(new CellRangeAddress(j, j + hc.Rowspan, nc, (hc.Colspan > 0 ? ec - 1 : ec)));
                        }
                        nc += hc.Colspan > 0 ? hc.Colspan : 1;

                        sheet.AutoSizeColumn(j); //表格列宽自动调整,只能支持英文和数字
                    }

                    sheet.AutoSizeColumn(j); //表格列宽自动调整,只能支持英文和数字
                    dt.Rows.Remove(headers[j]);
                }

                ICellStyle rowStyle = wookBook.CreateCellStyle();
                //写入行
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var dr = dt.Rows[i];
                    IRow row = sheet.CreateRow(i + headerCount);

                    // 设置行颜色
                    bool s = false;
                    if (dr.ForegroundColorIndex > 0)
                    {
                        s = true;
                        rowStyle.FillPattern = FillPattern.SolidForeground;
                        rowStyle.FillForegroundColor = dr.ForegroundColorIndex;
                    }

                    for (int j = 0; j < dr.Cells.Count; j++)
                    {
                        var rc = dr.Cells[j] ?? new ExcelTableCell();
                        string gbStr = Encoding.GetEncoding("gb2312").GetString(Encoding.Default.GetBytes(rc.Value ?? ""));
                        var cell = row.CreateCell(j);
                        if (rc.CellType == CellType.Numeric)
                        {
                            cell.SetCellType(rc.CellType);
                            double d;
                            double.TryParse(gbStr, out d);
                            cell.SetCellValue(d);
                        }
                        else
                        {
                            cell.SetCellValue(gbStr);
                        }

                        if (s) cell.CellStyle = rowStyle;
                        // 设置单元格颜色
                        if (rc.ForegroundColorIndex > 0)
                        {
                            rowStyle.FillPattern = FillPattern.SolidForeground;
                            rowStyle.FillForegroundColor = rc.ForegroundColorIndex;
                            cell.CellStyle = rowStyle;
                        }
                    }
                }
            }
            var ms = new MemoryStream();
            wookBook.Write(ms);
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xls");
            HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            HttpContext.Current.Response.End();
            ms.Flush();
            ms.Position = 0;
            ms.Close();
            ms = null;
        }
    }

    public class ExcelTable
    {
        public ExcelTable()
        {
            Rows = new List<ExcelTableRow>();
        }

        public ExcelTable(string tableName)
            : this()
        {
            TableName = tableName;
        }

        public ExcelTable(string tableName, int freezeCols, int freezeRows)
            : this()
        {
            TableName = tableName;
            FreezeCols = freezeCols;
            FreezeRows = freezeRows;
        }

        public string TableName { get; set; }
        public IList<ExcelTableRow> Rows { get; set; }

        /// <summary>
        /// 冻结的行数
        /// </summary>
        public int FreezeCols { get; set; }

        /// <summary>
        /// 冻结的列数
        /// </summary>
        public int FreezeRows { get; set; }
    }

    public class ExcelTableRow
    {
        public ExcelTableRow()
        {
            Cells = new List<ExcelTableCell>();
        }

        public ExcelTableRow(bool isHeader)
            : this()
        {
            IsHeader = isHeader;
        }

        public ExcelTableRow(params object[] cells)
            : this()
        {
            AddCells(cells);
        }

        public void AddCells(params object[] cells)
        {
            if (cells == null) return;
            foreach (var cell in cells)
            {
                Cells.Add(new ExcelTableCell(cell != null ? cell.ToString() : null));
            }
        }

        public void AddCell(ExcelTableCell cell)
        {
            Cells.Add(cell);
        }

        public bool IsHeader { get; set; }
        public IList<ExcelTableCell> Cells { get; set; }
        public short ForegroundColorIndex { get; set; }
    }

    public class ExcelTableCell
    {
        public ExcelTableCell()
        {
            CellType = CellType.String;
        }

        public ExcelTableCell(string value)
            : this()
        {
            Value = value;
        }

        public ExcelTableCell(string value, CellType cellType)
        {
            Value = value;
            CellType = cellType;
        }

        public ExcelTableCell(int value, CellType cellType)
        {
            Value = value.ToString();
            CellType = cellType;
        }

        public ExcelTableCell(string value, int colspan, int rowspan)
            : this()
        {
            Value = value;
            Colspan = colspan;
            Rowspan = rowspan;
        }

        public string Value { get; set; }
        public int Colspan { get; set; }
        public int Rowspan { get; set; }
        public CellType CellType { get; set; }

        public short ForegroundColorIndex { get; set; }

        public bool HasMerged()
        {
            return Colspan > 0 || Rowspan > 0;
        }
    }
}
